﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace Big_Data.Excel
{
    class Excel_File
    {
        public Application xlApp;
        public Workbook workBook;
        public Worksheet workSheet;

        public string path { get; set; }

        public Excel_File()
        {
            xlApp = new Application();
        }

        public void ExcelOpen()
        {
            workBook = xlApp.Workbooks.Open(path);
        }

        public void createSheet(string sheet)
        {
            workSheet = workBook.Worksheets.Add();
            workSheet.Name = sheet;
        }

        public List<string> ExcelScanNameSheet()
        {
            List<string> nameSheet = new List<string>();
            int nbSheet = workBook.Sheets.Count;
            for (int i = 1; i <= nbSheet; i++)
            {
                workSheet = workBook.Sheets.get_Item(i);
                nameSheet.Add(workSheet.Name);
            }
            return nameSheet;
        }
        public void OpenSheet(string sheet)
        {
            workSheet = workBook.Sheets[sheet];
        }

        public object[,] ExcelScanValueSheet()
        {
            workSheet.Columns.ClearFormats();
            workSheet.Rows.ClearFormats();
            Range excelRange = workSheet.UsedRange;

            object[,] valueArray = excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
            return valueArray;
        }

        public void CleanUp()
        {
            workBook.Close(false,path,null);
            xlApp.Quit();
            Marshal.ReleaseComObject(workSheet);
            Marshal.ReleaseComObject(workBook);
            Marshal.ReleaseComObject(xlApp);
        }
    }
}
